package com.cls.common.utils;

import java.io.*;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * POI调用帮助类
 *
 *
 */
public abstract class POIUtil {

	/**
	 * 复制工作表
	 *
	 * @param targetSheet
	 * @param sourceSheet
	 * @param copyStyle
	 * @throws Exception
	 */
	public static void copySheet(HSSFSheet targetSheet, HSSFSheet sourceSheet, boolean copyStyle) throws Exception {

		int cnt = targetSheet.getWorkbook().getNumCellStyles();
		int source = sourceSheet.getWorkbook().getNumCellStyles();
		for(int i = cnt; i < source; i++){
			HSSFCellStyle temp = targetSheet.getWorkbook().createCellStyle();
			temp.cloneStyleFrom(sourceSheet.getWorkbook().getCellStyleAt(i));
		}
		// 复制源表中的行
		int maxColumnNum = 0;
		for (int i = sourceSheet.getFirstRowNum(); i <= sourceSheet.getLastRowNum(); i++) {
			HSSFRow sourceRow = sourceSheet.getRow(i);
			HSSFRow targetRow = targetSheet.createRow(i);
			if (sourceRow != null) {
				copyRow(targetRow, sourceRow, copyStyle);
				if (sourceRow.getLastCellNum() > maxColumnNum) {
					maxColumnNum = sourceRow.getLastCellNum();
				}
			}
		}
		// 复制区域范围
		mergerRegion(targetSheet, sourceSheet);
		// 设置目标工作表的列宽
		for (int i = 0; i <= maxColumnNum; i++) {
			targetSheet.setColumnWidth(i, sourceSheet.getColumnWidth(i));
		}
	}

	/**
	 * 复制指定工作表的区域范围到其他指定工作表
	 *
	 * @param targetSheet
	 * @param sourceSheet
	 * @throws Exception
	 */
	public static void mergerRegion(HSSFSheet targetSheet, HSSFSheet sourceSheet) throws Exception {
		for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
			CellRangeAddress oldRange = sourceSheet.getMergedRegion(i);
			CellRangeAddress newRange = new CellRangeAddress(oldRange.getFirstRow(), oldRange.getLastRow(),
					oldRange.getFirstColumn(), oldRange.getLastColumn());
			targetSheet.addMergedRegion(newRange);
		}
	}

	/**
	 * 复制行
	 *
	 * @param targetRow
	 * @param sourceRow
	 * @param copyStyle
	 * @throws Exception
	 */
	public static void copyRow(HSSFRow targetRow, HSSFRow sourceRow, boolean copyStyle) throws Exception {
		// 设置行高
		targetRow.setHeight(sourceRow.getHeight());
		for (int i = sourceRow.getFirstCellNum(); i <= sourceRow.getLastCellNum(); i++) {
			HSSFCell sourceCell = sourceRow.getCell(i);
			HSSFCell targetCell = targetRow.getCell(i);
			if (sourceCell != null) {
				if (targetCell == null) {
					targetCell = targetRow.createCell(i);
				}
				// 拷贝单元格
				copyCell(targetCell, sourceCell, copyStyle);
				// 拷贝单元格注释
				copyComment(targetCell, sourceCell);
			}
		}
	}

	/**
	 * 复制行
	 *
	 * @param srcSheet
	 * @param desSheet
	 * @param srcNo
	 * @param desNo
	 * @throws Exception
	 */
	public static HSSFRow copyRow(HSSFSheet desSheet, int desNo, HSSFSheet srcSheet, int srcNo) throws Exception {
		// 设置行高
		HSSFRow srcRow = srcSheet.getRow(srcNo);
		HSSFRow desRow = desSheet.getRow(desNo);
		if(desRow == null){
			desRow = desSheet.createRow(desNo);
		}
		Iterator<Cell> it = srcRow.cellIterator();
		while (it.hasNext()) {
			Cell srcCell = it.next();
			Cell desCell = desRow.createCell(srcCell.getColumnIndex());
			copyCell(desCell, srcCell,true);
		}
		desSheet.setColumnWidth(desNo, srcSheet.getColumnWidth(srcNo));
		desRow.setHeight(srcRow.getHeight());
		return desRow;
	}

	public static HSSFRow copyRowAndSetData(HSSFSheet desSheet, int desNo, HSSFSheet srcSheet, int srcNo, String[] data) throws Exception {
		// 设置行高
		HSSFRow srcRow = srcSheet.getRow(srcNo);
		HSSFRow desRow = desSheet.getRow(desNo);
		if(desRow == null){
			desRow = desSheet.createRow(desNo);
		}
		Iterator<Cell> it = srcRow.cellIterator();
		while (it.hasNext()) {
			Cell srcCell = it.next();
			Cell desCell = desRow.createCell(srcCell.getColumnIndex());
			copyCell(desCell, srcCell,true);
		}
		addValueForRow(desRow, data);
		desSheet.setColumnWidth(desNo, srcSheet.getColumnWidth(srcNo));
		desRow.setHeight(srcRow.getHeight());
		return desRow;
	}

	/**
	 * 向指定工作表的指定行位置插入一行
	 *
	 * @param sheet
	 * @param rowNum
	 */
	public static boolean insertRow(HSSFSheet sheet, int rowNum, String[] data) {
		sheet.shiftRows(rowNum, sheet.getLastRowNum(), 1, true, false);
		HSSFRow row = sheet.createRow(rowNum);
		for(int i = 0; i < data.length; i++){
			HSSFCell cell = row.createCell(i);
			cell.setCellValue(data[i]);
		}
		return true;
	}


	public static boolean addValueForRow(HSSFRow row, String[] data) {
		for(int i = 0; i < data.length; i++){
			HSSFCell cell = row.getCell(i);
			cell.setCellValue(data[i]);
		}
		return true;
	}

	/**
	 * 向指定工作表的指定行位置插入一行
	 *
	 * @param sheet
	 * @param rowNum
	 */
	public static HSSFRow insertRow(HSSFSheet sheet, int rowNum) {
		sheet.shiftRows(rowNum, sheet.getLastRowNum(), 1, true, false);
		return sheet.createRow(rowNum);
	}

	/**
	 * 复制单元格
	 *
	 * @param targetCell
	 * @param sourceCell
	 * @param copyStyle
	 */
	public static void copyCell(Cell targetCell, Cell sourceCell, boolean copyStyle) {
		Workbook targetWorkbook = targetCell.getRow().getSheet().getWorkbook();
		Workbook sourceWorkbook = sourceCell.getRow().getSheet().getWorkbook();
		// 处理单元格样式
		if (copyStyle) {
			if (targetWorkbook == sourceWorkbook) {
				targetCell.setCellStyle(sourceCell.getCellStyle());
			} else {
				targetCell.setCellStyle(targetWorkbook.getCellStyleAt(sourceCell.getCellStyle().getIndex()));
			}
		}
		// 处理单元格内容
		switch (sourceCell.getCellTypeEnum()) {
			case STRING:
				targetCell.setCellValue(sourceCell.getStringCellValue());
				break;
			case NUMERIC:
				targetCell.setCellValue(sourceCell.getNumericCellValue());
				break;
			case FORMULA:
				targetCell.setCellFormula(sourceCell.getCellFormula());
				break;
			case BOOLEAN:
				targetCell.setCellValue(sourceCell.getBooleanCellValue());
				break;
			case ERROR:
				targetCell.setCellValue(sourceCell.getErrorCellValue());
				break;
			case BLANK:
				break;
			default:
				break;
		}
	}

	/**
	 * 复制备注
	 *
	 * @param targetCell
	 * @param sourceCell
	 * @throws Exception
	 */
	public static void copyComment(HSSFCell targetCell, HSSFCell sourceCell) throws Exception {
		HSSFComment comment = sourceCell.getCellComment();
		if (comment != null) {
			HSSFPatriarch targetPatriarch = targetCell.getRow().getSheet().createDrawingPatriarch();
			HSSFComment newComment = targetPatriarch.createComment(new HSSFClientAnchor());
			newComment.setAuthor(comment.getAuthor());
			newComment.setColumn(comment.getColumn());
			newComment.setFillColor(comment.getFillColor());
			newComment.setHorizontalAlignment(comment.getHorizontalAlignment());
			newComment.setLineStyle(comment.getLineStyle());
			newComment.setLineStyleColor(comment.getLineStyleColor());
			newComment.setLineWidth(comment.getLineWidth());
			newComment.setMarginBottom(comment.getMarginBottom());
			newComment.setMarginLeft(comment.getMarginLeft());
			newComment.setMarginTop(comment.getMarginTop());
			newComment.setMarginRight(comment.getMarginRight());
			newComment.setNoFill(comment.isNoFill());
			newComment.setRow(comment.getRow());
			newComment.setShapeType(comment.getShapeType());
			newComment.setString(comment.getString());
			newComment.setVerticalAlignment(comment.getVerticalAlignment());
			newComment.setVisible(comment.isVisible());
			targetCell.setCellComment(newComment);
		}
	}

	public static HSSFSheet createSheetByTemplate(HSSFWorkbook wb, String sheetName, String templatePath) throws Exception{
		InputStream is = new FileInputStream(templatePath);
		HSSFWorkbook templatesWb = new HSSFWorkbook(is);
		HSSFSheet sheet = templatesWb.getSheetAt(0);
		HSSFSheet targetSheet = wb.createSheet(sheetName);
		copySheet(targetSheet, sheet, true);
		return targetSheet;
	}

	public static HSSFSheet createSheetByTemplate(HSSFWorkbook wb, HSSFSheet srcSheet, String sheetName) throws Exception{
		HSSFSheet targetSheet = wb.createSheet(sheetName);
		copySheet(targetSheet, srcSheet, true);
		return targetSheet;
	}

	public static void mergerRegionInRow(HSSFSheet sheet, int rowNo, int firstCol, int lastCol) {
		if(firstCol == -1){
			firstCol = sheet.getRow(rowNo).getFirstCellNum();
		}
		if (lastCol == -1){
			lastCol = sheet.getRow(rowNo).getLastCellNum() - 1;
		}
		CellRangeAddress newRange = new CellRangeAddress(rowNo, rowNo,
				firstCol, lastCol);
		sheet.addMergedRegionUnsafe(newRange);
	}

	public static void mergerRegion(HSSFSheet sheet, int firstRow, int lastRow , int firstCol, int lastCol) {
		CellRangeAddress newRange = new CellRangeAddress(firstRow, lastRow,
				firstCol, lastCol);
		sheet.addMergedRegionUnsafe(newRange);
	}



	public static void main(String[] args) throws Exception {
		InputStream is = new FileInputStream("E:\\Work\\idea\\china-law-society\\src\\main\\resources\\templates\\export\\课题意向登记表.xls");
		HSSFWorkbook templatesWb = new HSSFWorkbook(is);
		HSSFSheet sheet = templatesWb.getSheetAt(0);
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet targetSheet = wb.createSheet("001");
		copySheet(targetSheet, sheet, true);

		String[] data = new String[]{"1-2","法理学","习近平总书记关于新时代政法工作重要论述研究","杜胜利","5","研究报告","3万字"};
		HSSFRow row = copyRowAndSetData(targetSheet, 4, targetSheet, 3, data);
		OutputStream os = new FileOutputStream("E://text.xls");
		wb.write(os);
	}

}
